package common.util;

import java.util.ArrayList;
import java.util.List;

/**
 * @Author: Sunhao
 * @CreateTime: 2025-03-26
 * @Description: 简化繁琐SQL拼接
 */
public class SQLGenerator {
    //加权平均电价
    public static String avgPowerPrice(int start, int end, int step) {

        StringBuilder sql = new StringBuilder();
        sql.append("SELECT\n");

        // 生成所有 PX 和 PRICEX 的加权计算表达式
        for (int i = start; i <= end; i += step) {
            String expression = String.format(
                    "  SUM(GREATEST(a.P%d, 0) * b.PRICE%d) / NULLIF(SUM(GREATEST(a.P%d, 0)), 0) AS P%d",
                    i, i, i, i
            );
            sql.append(expression);
            // 最后一个字段不加逗号
            sql.append((i + step <= end) ? ",\n" : "\n");
        }

        // 添加 FROM 和 JOIN 部分
        sql.append("FROM \"D5000\".mos_unit_pre_plan AS a\n");
        sql.append("LEFT JOIN \"D5000\".mos_unit_lmp_final AS b\n");
        sql.append("  ON a.\"PHYUNIT_ID\" = b.\"PHYUNIT_ID\"\n");
        sql.append("WHERE a.\"DATA_TIME\" = '${sj}';");
        return sql.toString();
       // System.out.println(sql.toString());
    }

    //加权平均电价——天
    private static String avgPowerPrice2(int start, int end, int step) {



        StringBuilder numerator = new StringBuilder();   // 分子部分
        StringBuilder denominator = new StringBuilder(); // 分母部分

        // 遍历时间点，拼接每个 P 和 PRICE 字段
        for (int i = start; i <= end; i += step) {
            String pField = "a.P" + i;
            String priceField = "b.PRICE" + i;

            // 分子部分：a.Pi * b.PRICEi
            numerator.append(pField).append(" * ").append(priceField);
            if (i < end) numerator.append(" + ");

            // 分母部分：NULLIF(a.Pi, 0)
            denominator.append("NULLIF(").append(pField).append(", 0)");
            if (i < end) denominator.append(" + ");
        }

        // 构建完整 SQL
        String sql = "SELECT \n" +
                "  a.\"DATA_TIME\", a.\"PHYUNIT_ID\", \n" +
                "  COALESCE(SUM(" + numerator.toString() + ") / SUM(" + denominator.toString() + "), 0) AS result\n" +
                "FROM \"D5000\".mos_unit_pre_plan AS a\n" +
                "LEFT JOIN \"D5000\".mos_unit_lmp_final AS b\n" +
                "  ON a.\"PHYUNIT_ID\" = b.\"PHYUNIT_ID\"\n" +
                "WHERE a.\"DATA_TIME\" = '${sj}'\n" +
                "GROUP BY a.\"DATA_TIME\", a.\"PHYUNIT_ID\";";
        return sql;
        //System.out.println(sql);
    }
    public static String avgPowerPrice3(int start, int end, int step) {
//        int start = 3;    // 起始时间点（P3）
//        int end = 288;    // 结束时间点（P288）
//        int step = 3;     // 步长

        List<String> columns = new ArrayList<>();
        // 固定列：CAPTION 和 DATA_TIME
        columns.add("c.\"CAPTION\"");
        columns.add("a.\"DATA_TIME\"");

        // 动态生成 P3 到 P288 的列（步长3）
        for (int i = start; i <= end; i += step) {
            String colExpr = String.format(
                    "COALESCE((a.P%d * b.PRICE%d) / (a.P%d), 0)  AS P%d" ,
                    i, i, i, i
            );
            columns.add(colExpr);
        }

        // 拼接完整 SQL
        String sql = "SELECT \n" +
                "  " + String.join(",\n  ", columns) + "\n" +
                "FROM \"D5000\".mos_unit_pre_plan AS a\n" +
                "LEFT JOIN \"D5000\".mos_unit_lmp_final AS b\n" +
                "  ON a.\"PHYUNIT_ID\" = b.\"PHYUNIT_ID\"\n" +
                "LEFT JOIN \"D5000\".\"GLBECO_PHYUNIT\" AS c\n" +
                "  ON a.\"PHYUNIT_ID\" = c.\"ID\"\n" +
                "WHERE a.\"DATA_TIME\" = '${sj}'";
        return sql;
    }
}
